package com.deng.etl.excel.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelRule;
import com.deng.maintenance.dao.impl.KPTSettingImpl;
import com.deng.maintenance.data.MaintenanceData;
import com.deng.maintenance.vo.KPIValueVOOld;
import com.deng.maintenance.vo.KPI_DicVO;
import com.deng.util.DateTools;

public class KPIImpl extends SQLHelper implements IExcelRule {

	public void importData(Workbook wb, String type_name,String dep_id) {
		try {
			Sheet sh=wb.getSheet(0);
			int rownum=sh.getRows();
			
			for(int i=2;i<rownum;i++){
				
				Cell ce_input=sh.getCell(7, i);
				String input=ce_input.getContents();
				if(input==null||"".equals(input)){
					continue;
				}
				Cell ce_id=sh.getCell(0, i);
				Cell ce_freq=sh.getCell(2,i);
				Cell ce_year=sh.getCell(5, i);
				Cell ce_time=sh.getCell(6, i);
				
				String id=ce_id.getContents();
				if(id==null||"".equals(id)){
					return ;
				}
				String freq=ce_freq.getContents();
				String year=ce_year.getContents();
				if(year==null||"".equals(year)){
					return ;
				}
				String time=ce_time.getContents();
				
				
				List<KPI_DicVO> list=searchKpi_Dic(Integer.valueOf(id), dep_id);
				if(list.size()==0){
					return;
				}
				KPI_DicVO kpi_dic=list.get(0);
				
				
				KPIValueVOOld vo=new KPIValueVOOld();
				vo.setDep_id(dep_id);
				vo.setKpi_id(Integer.valueOf(id));
				vo.setStatus("0");
				vo.setDuty_id(4);
				vo.setKpi_input_value(input);
				
				if("年度".equals(freq)){
					vo.setFrq_code("0001");
					
				}
				else if("半年度".equals(freq)){
					vo.setFrq_code("0002");
					
					if(time==null||"".equals(time)){
						return ;
					}
					
					if("上半年".equals(time)){
						vo.setHalf_year("up");
					}
					else if("下半年".equals(time)){
						vo.setHalf_year("down");
					}
					else{
						return  ;
					}
				}
				else if("季度".equals(freq)){
					vo.setFrq_code("0003");	
					if(time==null||"".equals(time)){
						return  ;
					}
					if("一季度".equals(time)){
						vo.setQuarter("1");
					}
					else if("二季度".equals(time)){
						vo.setQuarter("2");
					}
					else if("三季度".equals(time)){
						vo.setQuarter("3");
					}
					else if("四季度".equals(time)){
						vo.setQuarter("4");
					}
					else{
						return ;
					}
				}
				else if("月".equals(freq)){
					vo.setFrq_code("0004");
					if(time==null||"".equals(time)){
						return  ;
					}
					if("1月".equals(time)){
						vo.setMonth("1");
					}
					else if("2月".equals(time)){
						vo.setMonth("2");
					}
					else if("3月".equals(time)){
						vo.setMonth("3");
					}
					else if("4月".equals(time)){
						vo.setMonth("4");
					}
					else if("5月".equals(time)){
						vo.setMonth("5");
					}
					else if("6月".equals(time)){
						vo.setMonth("6");
					}
					else if("7月".equals(time)){
						vo.setMonth("7");
					}
					else if("8月".equals(time)){
						vo.setMonth("8");
					}
					else if("9月".equals(time)){
						vo.setMonth("9");
					}
					else if("10月".equals(time)){
						vo.setMonth("10");
					}
					else if("11月".equals(time)){
						vo.setMonth("11");
					}
					else if("12月".equals(time)){
						vo.setMonth("12");
					}
					else{
						return  ;
					}
					
				}
				else{
					return ;
				}
				vo.setYear(Integer.valueOf(year));
				
				KPTSettingImpl kpiset=new KPTSettingImpl();
				
				vo.setKpi_score(kpiset.count(Integer.valueOf(id), input));
				
				List<KPIValueVOOld> tmplist=searchKPIValues(vo);
				if(tmplist.size()==0){
					List<KPIValueVOOld> tmp=new ArrayList<KPIValueVOOld>();
					tmp.add(vo);
					insertKPIValues(tmp);
				}
				else{
					KPIValueVOOld tmpvo=tmplist.get(0);
					if(tmpvo.getStatus()=="0"){
						vo.setId(tmpvo.getId());
						vo.setCreate_time(tmpvo.getCreate_time());
						List<KPIValueVOOld> tmp=new ArrayList<KPIValueVOOld>();
						tmp.add(vo);
						updateKPIValues(tmp);
					}
					
				}
				
			}
			
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
	}

	
	public List<KPI_DicVO> searchKpi_Dic(Integer kpi_id ,String dep_id) {
		// TODO Auto-generated method stub
		List<KPI_DicVO> ret =new ArrayList<KPI_DicVO>();
		String select ="select * from kpi_dic";
		String where="";
		if(kpi_id>=0){
			where +=" kpi_id="+kpi_id;
		}
		if(dep_id!=null&&!"".equals(dep_id)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" dep_id='"+dep_id+"'";
		}
		if(!"".equals(where)){
			where =" where "+where;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where+" order by kpi_id");
			
			while(rs.next()){
				KPI_DicVO vo = new KPI_DicVO();
				vo.setAudi_freq(rs.getString("audi_freq"));
				vo.setDep_id(rs.getString("dep_id"));
				vo.setExpression(rs.getString("expression"));
				vo.setKpi_define(rs.getString("kpi_define"));
				vo.setKpi_id(rs.getInt("kpi_id"));
				vo.setKpi_name(rs.getString("kpi_name"));
				vo.setScore_method(rs.getString("score_method"));
				vo.setTarget_value(rs.getDouble("target_value")+"");
				
				if(rs.getDouble("target_value_max")!=0){
					vo.setTarget_value_max(rs.getDouble("target_value_max")+"");
				}
				else{
					vo.setTarget_value_max("");
				}
				vo.setWeight(rs.getDouble("weight")+"");
				vo.setCreate_time(rs.getTimestamp("create_time"));
				vo.setModify_time(rs.getTimestamp("modify_time"));
				vo.setDuty_id(rs.getInt("duty_id"));
				vo.setDuty_name(rs.getString("duty_name"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return ret;
	}
	
	public List<KPIValueVOOld> searchKPIValues(KPIValueVOOld vo_p){
		List<KPIValueVOOld> ret=new ArrayList<KPIValueVOOld>();
		String select ="select * from kpi_value";
		String where="";
		
		if(vo_p.getDep_id()!=null&&!"".equals(vo_p.getDep_id())){
			where +=" dep_id='"+vo_p.getDep_id()+"'";
		}
		if(vo_p.getKpi_id()!=null&&!"".equals(vo_p.getKpi_id())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" kpi_id="+vo_p.getKpi_id();
		}
		if(vo_p.getDuty_id()!=null&&!"".equals(vo_p.getDuty_id())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" duty_id="+vo_p.getDuty_id();
		}
		if(vo_p.getYear()!=null&&!"".equals(vo_p.getYear())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" year="+vo_p.getYear();
		}
		if(vo_p.getFrq_code()!=null&&!"".equals(vo_p.getFrq_code())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" frq_code='"+vo_p.getFrq_code()+"'";
		}
		
		if(vo_p.getQuarter()!=null&&!"".equals(vo_p.getQuarter())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" quarter='"+vo_p.getQuarter()+"'";
		}
		if(vo_p.getHalf_year()!=null&&!"".equals(vo_p.getHalf_year())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" half_year='"+vo_p.getHalf_year()+"'";
		}
		if(vo_p.getMonth()!=null&&!"".equals(vo_p.getMonth())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" month='"+vo_p.getMonth()+"'";
		}
		
		if(!"".equals(where)){
			where =" where "+where;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where);
			
			while(rs.next()){
				KPIValueVOOld vo = new KPIValueVOOld();
				vo.setCreate_time(rs.getTimestamp("create_time"));
				vo.setDep_id(rs.getString("dep_id"));
				vo.setFrq_code(rs.getString("frq_code"));
				vo.setId(rs.getInt("id"));
				vo.setKpi_id(rs.getInt("kpi_id"));
				vo.setKpi_input_value(rs.getDouble("kpi_input_value")+"");
				vo.setKpi_score(rs.getDouble("kpi_score")+"");
				vo.setModify_time(rs.getTimestamp("modify_time"));
				vo.setYear(rs.getInt("year"));
				vo.setDuty_id(rs.getInt("duty_id"));
				vo.setQuarter(rs.getString("quarter"));
				vo.setMonth(rs.getString("month"));
				vo.setHalf_year(rs.getString("half_year"));
				vo.setYear1(rs.getString("year1"));
				vo.setStatus(rs.getString("status"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
		
		return ret;
	}
	public String insertKPIValues(List<KPIValueVOOld> vos) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="insert into kpi_value(dep_id,kpi_id,kpi_input_value,kpi_score,create_time,modify_time,frq_code,year,duty_id,quarter,month,half_year,year1,status) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = getConnect();
		
		try{
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			for(int i=0;i<vos.size();i++){
				KPIValueVOOld vo=vos.get(i);
				pst.setString(1, vo.getDep_id());
				pst.setInt(2, vo.getKpi_id());
				pst.setDouble(3, Double.valueOf(vo.getKpi_input_value()));
				pst.setDouble(4, Double.valueOf(vo.getKpi_score()));
				Timestamp time=DateTools.getTimestamp(new Date());
				pst.setTimestamp(5, time);
				pst.setTimestamp(6, time);
				pst.setString(7, vo.getFrq_code());
				pst.setInt(8, vo.getYear());
				pst.setInt(9, vo.getDuty_id());
				pst.setString(10, vo.getQuarter());
				pst.setString(11, vo.getMonth());
				pst.setString(12, vo.getHalf_year());
				pst.setString(13, vo.getYear1());
				pst.setString(14, vo.getStatus());
				pst.addBatch();
			}
			pst.executeBatch();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}
	
	public String updateKPIValues(List<KPIValueVOOld> vos) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="update kpi_value set dep_id=?,kpi_id=?,kpi_input_value=?,kpi_score=?" +
				",modify_time=?,frq_code=?,year=?,duty_id=?,quarter=?,month=?,half_year=?,year1=?,status=? where id=? ";
		Connection con = getConnect();
		
		try{
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			for(int i=0;i<vos.size();i++){
				KPIValueVOOld vo=(KPIValueVOOld)vos.get(i);
				pst.setString(1, vo.getDep_id());
				pst.setInt(2, vo.getKpi_id());
				pst.setDouble(3, Double.valueOf(vo.getKpi_input_value()));
				pst.setDouble(4, Double.valueOf(vo.getKpi_score()));
				Timestamp time=DateTools.getTimestamp(new Date());
				pst.setTimestamp(5, time);
				pst.setString(6, vo.getFrq_code());
				pst.setInt(7, vo.getYear());
				pst.setInt(8, vo.getDuty_id());
				pst.setString(9, vo.getQuarter());
				pst.setString(10, vo.getMonth());
				pst.setString(11, vo.getHalf_year());
				pst.setString(12, vo.getYear1());
				pst.setString(13, vo.getStatus());
				pst.setInt(14, vo.getId());
				pst.addBatch();
			}
			pst.executeBatch();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}

}
